import json
import logging

import pymysql as pymysql

from config import db_host, db_port, db_name, db_passwd, db_user


def get_db():
    db = pymysql.connect(host=db_host, port=db_port, db=db_name,
                         user=db_user, passwd=db_passwd)
    return db


def get_cookie():
    cursor = get_db().cursor()
    sql = 'select `value` from fa_config where name = "sp_cookie" limit 1 ;'
    cursor.execute(sql)
    row = ""
    for rowt in cursor.fetchall():
        row = rowt
    return [row[0]]


def get_enable_cookies():
    cursor = get_db().cursor()
    sql = 'select `value` from fa_config where name = "sp_cookie" limit 1 ;'
    cursor.execute(sql)
    row = ""
    for rowt in cursor.fetchall():
        row = rowt
    r = json.loads(row[0])
    cs = []
    for kv in r.items():
        if kv[1]["usable"] == False:
            continue;
        cs.append(kv[1]['cookie'])
    return cs


def batch_insert_ranks(ranks):
    if len(ranks) == 0:
        return False
    db = get_db()
    t = [[
        r['product'],
        r['keyword'],
        r['rank'],
        r['desc'],
        r['video_create_time'],
        r['video_url'],
        r['video_duration'],
        r['author'],
        r['statistics_digg_count'],
        r['statistics_play_count'],
        r['statistics_share_count'],
        r['statistics_collect_count'],
        r['text_extra'],
        r['create_time'],
        r['task_id'],
        r['video_type'],
        r['dy_keyword_id'],
        r['dy_product_id'],

    ] for r in ranks]
    with db:
        with db.cursor()as cursor:
            sql = 'INSERT INTO `video`.`fa_video_rank`(`product`, `keyword`, `rank`, `desc`,' \
                  ' `video_create_time`, `video_url`, `video_duration`, `author`, `statistics_digg_count`,' \
                  ' `statistics_play_count`, `statistics_share_count`, `statistics_collect_count`, `text_extra`, ' \
                  '`createtime`,`sp_task_id`,`video_type`,`dy_keyword_id`,`dy_product_id`)' \
                  ' VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s);'
            s = cursor.executemany(sql, t)
        db.commit()
    return True


def get_tasks(state, begin, end):
    cursor = get_db().cursor()
    sql = 'select * from fa_sp_task where status = %s and task_begin_time between %s and %s;'

    cursor.execute(sql, [state, begin, end])
    rows = []
    for row in cursor.fetchall():
        rows.append(row)
    return rows


def update_tasks(state, ids):
    if len(ids) == 0:
        return 0
    db = get_db()
    with db:
        with db.cursor()as cursor:
            sql = 'update fa_sp_task set status = %s where id in %s;'
            s = cursor.execute(sql, [state, ids])
        db.commit()
    return s


def delete_ranks_by_task_ids(task_ids):
    db = get_db()
    with db:
        with db.cursor()as cursor:
            sql = 'delete from  fa_video_rank where sp_task_id in %s;'
            s = cursor.execute(sql, [task_ids])
        db.commit()
    return s
